<?php
defined ( 'SYSPATH' ) or die ( 'No direct access allowed.' );
/*
 * 采购收货明细报表
 * 
 */
class Controller_quality_detail extends Controller_Admin{
	
    public function before()
    {
    	try {
    		  ACL::Required('qc detail report');
    	} catch ( HTTP_Exception_403 $e ) {
				Message::error('sorry，你没有权限进这个目录');
    		$this->request->redirect ( URL::base (true) );
    	}
        parent::before();
    }
	
	//
	public function Action_index()
	{
		$this->title = __("质检明细报表");
		$view = View::factory('from/qualitydetails');
		$enterprise_dominant = Kohana::$config->load('enterprise_dominant')->as_array();
		$view->set('enterprise_dominant',$enterprise_dominant);//主体
		$this->response->body($view);		
	}
	
	public function Action_select()
	{
        $this->title = __("质检明细报表");
		$sql = " SELECT qd.id,qd.warehouseid,qd.enterprise_dominant,qd.recieve_detail_id,qd.invoice_date,qd.batch_code,qd.sku,qd.sku_name,qd.sku_standard,
				  qd.storage_position,qd.supplier_id,qd.qualified_quantity,qd.check_quantity,qd.unquality_count,
 				  pd.quantity,pd.purchaseorder_id,qd.export_tax_rebate";
		$sel_sql = $this->Select_sql($_GET);
		
		
		if($sel_sql)
		{
			$sum = DB::query(Database::SELECT, $sql.$sel_sql)
			->execute('newerp')
			->as_array();
		}
        $pagination = Pagination::factory(array(
            'items_per_page' => 10,
            'total_items' => count($sum),
            'uri' => $this->request->uri(),
        ));
        if(isset($_GET['down'])){
            $this->Action_load($sum);die;
        }
        $current_page = isset($_GET['page']) ? $_GET['page'] : 1;
        $limit = "  LIMIT " . (($current_page - 1) * 10) . ",10";
        $quality_lists = DB::query(Database::SELECT, $sql.$sel_sql.$limit)
            ->execute('newerp')
            ->as_array();
		foreach($quality_lists as $key=>$quality_list){
			$sql="SELECT `export_tax_rebate`
                              FROM `purchaseorders`
                              WHERE `id`=".$quality_list['purchaseorder_id'];
			$export_tax_rebate=DB::query(Database::SELECT,$sql)->execute('newerp')->get('export_tax_rebate');
			$quality_lists[$key]['export_tax_rebate']=$export_tax_rebate;
		}
		$vmi_warehouse = Kohana::$config->load('vmi_warehouse')->as_array();
		$enterprise_dominant = Kohana::$config->load('enterprise_dominant')->as_array();
//		echo "<pre>";print_r($quality_lists);echo "</pre>";die;
		$view = View::factory('from/qualitydetails');
		$view->set('vmi_warehouse',$vmi_warehouse);//仓库
		$view->set('enterprise_dominant',$enterprise_dominant);//主体
		$view->set('quality_lists',$quality_lists);//结果
		$view->set('pagination',$pagination);

        $this->response->body($view);

    }
	
	//获取sum值
	public function Action_select_sum()
	{
		$re = array();
		
		//采购总量
// 		$sum_purchase_sql = " SELECT distinct b. SUM(b.purchase_quantity) ";
// 		$sum_purchase_sql_ = "";
		
		//质检总量，合格总量，不合格总量
		$sum_sql = " SELECT COUNT(qd.id) AS ids ";
		$sum_sql .= " ,SUM(qd.check_quantity) AS sum_check_quantity ";
		$sum_sql .= " ,SUM(qd.qualified_quantity) AS sum_qualified_quantity ";
		$sum_sql .= " ,SUM(qd.unquality_count) AS sum_unquality_count ";
			
		$sql = $this->Select_sql($_POST);
		
 		if($sql)
 		{

			$sum_quality = DB::query(Database::SELECT, 'SELECT sum(ids) ids ,sum(sum_check_quantity) sum_check_quantity,
sum(sum_qualified_quantity) sum_qualified_quantity,
sum(sum_unquality_count) sum_unquality_count FROM ('.$sum_sql.$sql.') t')->execute('newerp')->current();
			
			$re['sum_ids'] = isset($sum_quality['ids'])?$sum_quality['ids']:0;
			$re["sum_check_quantity"] = isset($sum_quality["sum_check_quantity"])?$sum_quality["sum_check_quantity"]:0;
			$re["sum_qualified_quantity"] = isset($sum_quality["sum_qualified_quantity"])?$sum_quality["sum_qualified_quantity"]:0;
			$re["sum_unquality_count"] = isset($sum_quality["sum_unquality_count"])?$sum_quality["sum_unquality_count"]:0;
			
			if($re["sum_check_quantity"] > 0)
			{
				$re['qualified_rate'] = round($re["sum_qualified_quantity"]/$re["sum_check_quantity"]*100,2);
			}
			else 
			{
				$re['qualified_rate'] = 0;
			}
 		} 

 		echo json_encode($re);exit;
	}
	
	
	
	protected function Select_sql(array $condtion)
	{
		
		$sql = "";
		
		$sql .= " FROM check_quality_details AS qd ";
		$sql .= " ,purchaseorder_details AS pd ";
		$sql .= " WHERE qd.purchaseorder_id = pd.purchaseorder_id ";
		$sql .= " AND qd.sku = pd.sku ";
		
		//质检时间
		if(isset($condtion['invoice_date_from']) and $condtion['invoice_date_from'])
		{
			$sql .= " AND qd.invoice_date > '".$condtion['invoice_date_from']."'";
		}
		
		if(isset($condtion['invoice_date_to']) and $condtion['invoice_date_to'])
		{
			$sql .= " AND qd.invoice_date <= '".$condtion['invoice_date_to']." 23:59:59 '";
		}

        if(isset($condtion['batch_code']) and $condtion['batch_code'])
        {
            $sql .= " AND qd.batch_code = '".$condtion['batch_code']."'";
        }
		
		//SKU
		if(isset($condtion['sku']) and $condtion['sku'])
		{
			$sql .= " AND qd.sku = '".$condtion['sku']."'";
		}

		//主体
		if(isset($condtion['enterprise_dominant']) and $condtion['enterprise_dominant'])
		{
			$sql .= " AND qd.enterprise_dominant = '".$condtion['enterprise_dominant']."'";
		}

		//退税
		if(isset($condtion['export_tax_rebate']))
		{
			$sql .= " AND qd.export_tax_rebate = '".$condtion['export_tax_rebate']."'";
		}
		
		
		//供应商
		if(isset($condtion['supplier_id']) and intval($condtion['supplier_id']))
		{		
				$supplier_id = $condtion['supplier_id'];			
				$sql .= " AND qd.supplier_id = $supplier_id ";
		}
		$sql .= " group by qd.id ";//20141121质检明细报表重复显示添加一行
		$sql .= " ORDER  BY  qd.invoice_date DESC";
		
		//质检，SKU，供应商，采购量，合格量
		if(isset($condtion['order_by']) and $condtion['order_by'] == 1 )
		{
			$sql .= " , qd.invoice_date DESC";
		}
		elseif (isset($condtion['order_by']) and $condtion['order_by'] == 2)
		{
			$sql .= " , qd.sku ";
		}
		elseif (isset($condtion['order_by']) and $condtion['order_by'] == 3)
		{
			$sql .= " , qd.supplier_id ";
		}
		elseif (isset($condtion['order_by']) and $condtion['order_by'] == 4)
		{
			$sql .= " , pd.quantity ";
		}
		elseif (isset($condtion['order_by']) and $condtion['order_by'] == 5)
		{
			$sql .= " , qd.qualified_quantity ";
		}
		else 
		{
			$sql .= "  , qd.invoice_date DESC ";
		}
		
//		//一次多少条
//		if(isset($condtion['limit']))
//		{
//			$sql .= " LIMIT ".$condtion['limit'];
//		}
//
//
//		//偏移
//		if(isset($condtion['offset']))
//		{
//			$sql .= " OFFSET ".$condtion['offset'];
//		}
		
		return $sql;
		
	}
	
	public function Action_load($quality_lists)
	{
		set_time_limit(0);
		ini_set('memory_limit','1024M');
		
//		$sql = " SELECT qd.id,qd.warehouseid,qd.enterprise_dominant,qd.recieve_detail_id,qd.invoice_date,qd.batch_code,qd.sku,qd.sku_name,qd.sku_standard,
//				  qd.storage_position,qd.supplier_id,qd.qualified_quantity,qd.check_quantity,qd.unquality_count,
// 				  pd.quantity,pd.purchaseorder_id";
//
//		$sel_sql = $this->Select_sql($_GET);
//
//
//		if($sel_sql)
//		{
//			$quality_lists = DB::query(Database::SELECT, $sql.$sel_sql)
//			->execute('newerp')
//			->as_array();
//		}
		
		if($quality_lists)
		{

			foreach($quality_lists as $key=>$quality_list){
				$sql="SELECT `export_tax_rebate`
                              FROM `purchaseorders`
                              WHERE `id`=".$quality_list['purchaseorder_id'];
				$export_tax_rebate=DB::query(Database::SELECT,$sql)->execute('newerp')->get('export_tax_rebate');
				$quality_lists[$key]['export_tax_rebate']=$export_tax_rebate;
			}
			require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
			
			$objPHPExcel      = new PHPExcel();
			$objPHPExcel->getProperties()->setCreator("YKS")
			->setLastModifiedBy("YKS")
			->setTitle("Office 2003 XLSX Test Document")
			->setSubject("Office 2003 XLSX Test Document")
			->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
			->setKeywords("office 2003 openxml php")
			->setCategory("Test result file");
			$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
			
			$objActSheet->setCellValue('A1', '序号');
			$objActSheet->setCellValue('B1', '质检日期');
			$objActSheet->setCellValue('C1', '收货明细单号');
			$objActSheet->setCellValue('D1', 'VMI预约单号');
			$objActSheet->setCellValue('E1', '退税类型');
			$objActSheet->setCellValue('F1', 'SKU');
			$objActSheet->setCellValue('G1', '中文名称');
			$objActSheet->setCellValue('H1', '仓库');
			$objActSheet->setCellValue('I1', '主体');
			$objActSheet->setCellValue('J1', '规格');
			$objActSheet->setCellValue('K1', '储位');
			$objActSheet->setCellValue('L1', '供应商');
			$objActSheet->setCellValue('M1', '采购数');
			$objActSheet->setCellValue('N1', '质检数');
			$objActSheet->setCellValue('O1', '合格数');
			$objActSheet->setCellValue('P1', '不合格数');
			$objActSheet->setCellValue('Q1', '良品率');
            $vmi_warehouse = Kohana::$config->load('vmi_warehouse')->as_array();
            $enterprise_dominant = Kohana::$config->load('enterprise_dominant')->as_array();
			foreach ($quality_lists as $key => $quality_list)
			{
				$objActSheet->setCellValue('A'.($key+2), $key + 1);
				$objActSheet->setCellValue('B'.($key+2),$quality_list['invoice_date'] );
				$objActSheet->setCellValue('C'.($key+2),$quality_list['recieve_detail_id'] );//purchaseorder_id
				$objActSheet->setCellValue('D'.($key+2),$quality_list['purchaseorder_id'] );//purchaseorder_id
				$objActSheet->setCellValue('E'.($key+2),$quality_list['export_tax_rebate']==0?'非退税':'退税' );
				$objActSheet->setCellValue('F'.($key+2),$quality_list['sku'] );
				$objActSheet->setCellValue('G'.($key+2),$quality_list['sku_name'] );
				$objActSheet->setCellValue('H'.($key+2),$vmi_warehouse[$quality_list['warehouseid']]);
				$objActSheet->setCellValue('I'.($key+2),$enterprise_dominant[$quality_list['enterprise_dominant']]);
				$objActSheet->setCellValue('J'.($key+2),$quality_list['sku_standard'] );
				$objActSheet->setCellValue('K'.($key+2),$quality_list['storage_position'] );
				$objActSheet->setCellValue('L'.($key+2),Basic::getnameByid($quality_list['supplier_id']) );
				$objActSheet->setCellValue('M'.($key+2),$quality_list['quantity'] );
				$objActSheet->setCellValue('N'.($key+2),$quality_list['check_quantity'] );
				$objActSheet->setCellValue('O'.($key+2),$quality_list['qualified_quantity'] );
				$objActSheet->setCellValue('P'.($key+2),$quality_list['unquality_count'] );
				$objActSheet->setCellValue('Q'.($key+2),$quality_list['check_quantity']>0?round(100*$quality_list['qualified_quantity']/$quality_list['check_quantity'],2)."%":"--" );

			}
			
			$objPHPExcel->getActiveSheet()->setTitle('质检明细报表');
			$objPHPExcel->setActiveSheetIndex(0);
			$day=date("m-d");
			$filename=$day.'质检明细报表.xls';
            ob_end_clean();
			header("Content-Type: application/vnd.ms-excel; charset=utf-8");
			header('Content-Disposition: attachment;filename='.$filename);
			header('Cache-Control: max-age=0');
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter->save('php://output');
			exit;
		}
		
	} 
}
